import datetime
import glob
from pathlib import Path

import numpy as np

from StressAna.Lib.DataAna import AnkeDataAna
from StressAna.Lib.Model import Globalvariables
from StressAna.Lib.DBProc.DBHelper import DBHelper
from StressAna.Lib.Utils import DateTimeUtil, DrawUtil


def update_code():
    mysql_db = DBHelper()
    for i, sta in enumerate(Globalvariables.STATION_dict):
        code, channel = sta.split('-')
        sql = f"SELECT * from device_info where code='{code}' and channel='{channel}'"
        result = mysql_db.select(sql)
        if result:
            continue
        data_dict = {'code': code, 'nid': Globalvariables.nid_list[i], 'channel': channel,
                     'length': Globalvariables.STATION_CD[i], 'wall_thick': Globalvariables.STATION_BH[i],
                     'hole_depth': Globalvariables.STATION_KS[i], 'initial_pressure': Globalvariables.STATION_CSY[i]}
        sql = f"insert into device_info ({','.join(data_dict.keys())}) values {tuple(data_dict.values())}"
        print(sql)
        # break
        mysql_db.insert(sql)


def update_td_data():
    mysql_db = DBHelper()
    # file_list = [r'D:\LK\TMV煤矿项目\应力数据\obsdata\1\G01\G01_2024-02-01.txt',
    #              r'D:\LK\TMV煤矿项目\应力数据\obsdata\1\G02\G02_2024-02-01.txt']
    sql = f"SELECT d.code, s.date FROM stress_data s inner join device_info d on s.device_id=d.id"
    result = mysql_db.select(sql)

    for file in glob.glob(r'D:\LK\TMV煤矿项目\应力数据\obsdata\1\**\*.txt', recursive=True):
        # for file in file_list:
        _name = Path(file).name[:3]
        _date = Path(file).name[4:14]
        if (_name, datetime.datetime.strptime(_date, "%Y-%m-%d")) in result:
            continue
        print(file)
        with open(file, 'r') as f:
            lines = f.read().splitlines()
        lines_list = [x.split(',') for x in lines[1:]]
        raw_data = np.array(lines_list).transpose(1, 0)

        for chn in [1, 2]:
            date_time_list = ','.join(raw_data[0])
            value_list = ','.join(raw_data[int(chn)])
            if device_id := mysql_db.select(f"SELECT id from device_info where code='{_name}' and channel='{chn}'"):
                data_dict = {'device_id': device_id[0][0], 'date': _date, 'date_time': date_time_list,
                             'value': value_list}
                mysql_db.insert(
                    f"insert into stress_data ({','.join(data_dict.keys())}) values {tuple(data_dict.values())}")


def update_sk_data():
    mysql_db = DBHelper()

    sql = f"SELECT d.code, s.date FROM stress_data s inner join device_info d on s.device_id=d.id"
    result = mysql_db.select(sql)
    # file_list = [r'D:\LK\Project\data-plot\StressAna\Data\钻孔应力传感器-10-_20240125143622.xlsx',
    #              r'D:\LK\Project\data-plot\StressAna\Data\钻孔应力传感器-11-_20240125143639.xlsx']
    # for file in file_list:
    for file in glob.glob(r'D:\LK\TMV煤矿项目\应力数据\AK\思科赛德数据\**\*.xlsx', recursive=True):
        print(file)
        ak_data = AnkeDataAna.AnkeAna(file)
        df = ak_data.df
        df = df.sort_values('time', ascending=True)
        for sid, dfi in df.groupby('sID'):
            if not mysql_db.select(f"SELECT * from device_info where code='{sid}'"):
                for chn in ['1', '2']:
                    data_dict = {'code': sid, 'nid': sid, 'channel': chn,
                                 'length': '', 'wall_thick': '', 'hole_depth': '', 'initial_pressure': ''}
                    sql = f"insert into device_info ({','.join(data_dict.keys())}) values {tuple(data_dict.values())}"
                    mysql_db.insert(sql)

        df['date'] = df['time'].apply(lambda x: str(x)[:10])
        df['time'] = df['time'].apply(lambda x: str(x))
        df['ch1'] = df['ch1'].apply(lambda x: str(x))
        df['ch2'] = df['ch2'].apply(lambda x: str(x))

        for _date, dfi in df.groupby('date'):
            _name = dfi.iloc[0]['sID']
            if (_name, datetime.datetime.strptime(_date, "%Y-%m-%d")) in result:
                continue
            date_time_list = ','.join(dfi['time'].to_list())
            for chn in [1, 2]:
                value_list = ','.join(dfi[f'ch{chn}'].to_list())
                if device_id := mysql_db.select(f"SELECT id from device_info where code='{_name}' and channel='{chn}'"):
                    data_dict = {'device_id': device_id[0][0], 'date': _date, 'date_time': date_time_list,
                                 'value': value_list}
                    mysql_db.insert(
                        f"insert into stress_data ({','.join(data_dict.keys())}) values {tuple(data_dict.values())}")


#
#
def _sel():
    mysql_db = DBHelper()
    # _sid_list = ['G01-1', 'G02-1']
    # start_datetime = '2024-02-01 00:00:00'
    # end_datetime = '2024-02-01 22:00:00'
    _sid_list = ['10-1', '11-1']
    start_datetime = '2024-01-20 00:00:00'
    end_datetime = '2024-01-20 22:00:00'
    start_date = DateTimeUtil.formatDateTimeBy(DateTimeUtil.parseDateTimeBy(start_datetime, "%Y-%m-%d %H:%M:%S"),
                                               '%Y-%m-%d')
    end_date = DateTimeUtil.formatDateTimeBy(DateTimeUtil.parseDateTimeBy(end_datetime, "%Y-%m-%d %H:%M:%S"),
                                             '%Y-%m-%d')
    date_list = DateTimeUtil.get_date_str_list_by_date_range(start_date, end_date, '%Y-%m-%d')
    _date = ','.join([f"'{x}'" for x in date_list])
    data_list = []
    for sid in _sid_list:
        iname, ichn = sid.split('-')
        sql = f"SELECT d.code, d.channel, s.date_time, s.value FROM stress_data s inner join device_info d on s.device_id=d.id WHERE d.code='{iname}' and d.channel='{ichn}' and s.date in ({_date})"
        result = mysql_db.select(sql)
        srcX = []
        srcY = []
        dstX = []
        dstY = []
        for row in result:
            row_date_time_list = row[2].split(',')
            row_value_list = row[3].split(',')
            srcX.extend(row_date_time_list)
            srcY.extend(row_value_list)
        for i, date_time in enumerate(srcX):
            if start_datetime <= date_time <= end_datetime:
                dstX.append(date_time)
                dstY.append(srcY[i])
        X = [datetime.datetime.strptime(x, "%Y-%m-%d %H:%M:%S") for x in dstX]
        Y = [float(x) for x in dstY]
        data_list.append((X, Y))
    DrawUtil.draw_util(data_list, labels=_sid_list)


def del_sk_data():
    mysql_db = DBHelper()
    sql = f"DELETE from stress_data where device_id>=65"
    mysql_db.delete(sql)


if __name__ == '__main__':
    # update_code()
    # update_td_data()
    update_sk_data()
    # _sel()
